package com.fl.nj.util;

import com.fl.nj.config.DBConfig;
import com.fl.nj.entity.DatabaseConfig;
import com.fl.nj.entity.TableColumn;
import com.fl.nj.entity.TableSchema;
import com.fl.nj.pool.ConnectionPoolManager;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 数据库模式工具类
 * 用于获取数据库的表结构和字段信息
 */
public class SchemaUtil {

    /**
     * 根据数据库配置获取指定表的字段信息
     * @param dbConfig 数据库配置
     * @param tableName 表名
     * @return 表字段信息列表
     * @throws SQLException 数据库操作异常
     */
    public static List<TableColumn> getTableColumns(DatabaseConfig dbConfig, String tableName) throws SQLException {
        Connection conn = null;
        DatabaseMetaData metaData = null;
        ResultSet rs = null;
        List<TableColumn> columns = new ArrayList<>();

        try {
            // 初始化连接池并获取连接
            String poolId = "temp_schema_pool_" + System.currentTimeMillis();
            ConnectionPoolManager.initDataSource(poolId, convertToDBConfig(dbConfig));
            conn = ConnectionPoolManager.getConnection(poolId);
            metaData = conn.getMetaData();

            // 获取表的字段信息
            // 参数说明: catalog, schemaPattern, tableNamePattern, columnNamePattern
            rs = metaData.getColumns(null, null, tableName, "%");

            int order = 1;
            while (rs.next()) {
                TableColumn column = new TableColumn();
                column.setColumnName(rs.getString("COLUMN_NAME"));
                column.setColumnType(rs.getString("TYPE_NAME"));
                column.setColumnLength(rs.getInt("COLUMN_SIZE"));
                column.setIsNullable("YES".equals(rs.getString("IS_NULLABLE")));
                column.setDefaultValue(rs.getString("COLUMN_DEF"));
                column.setColumnComment(rs.getString("REMARKS"));
                column.setColumnOrder(order++);
                column.setIsPrimaryKey(false); // 默认不是主键，后面会单独处理

                columns.add(column);
            }

            // 获取主键信息
            rs.close();
            rs = metaData.getPrimaryKeys(null, null, tableName);
            while (rs.next()) {
                String primaryKeyName = rs.getString("COLUMN_NAME");
                for (TableColumn column : columns) {
                    if (column.getColumnName().equals(primaryKeyName)) {
                        column.setIsPrimaryKey(true);
                        break;
                    }
                }
            }

            // 关闭临时连接池
            ConnectionPoolManager.closeDataSource(poolId);

        } finally {
            closeResources(rs, null, conn);
        }

        return columns;
    }

    /**
     * 根据数据库配置获取所有表名
     * @param dbConfig 数据库配置
     * @return 表名列表
     * @throws SQLException 数据库操作异常
     */
    public static List<String> getAllTableNames(DatabaseConfig dbConfig) throws SQLException {
        Connection conn = null;
        DatabaseMetaData metaData = null;
        ResultSet rs = null;
        List<String> tableNames = new ArrayList<>();

        try {
            // 初始化连接池并获取连接
            String poolId = "temp_schema_pool_" + System.currentTimeMillis();
            ConnectionPoolManager.initDataSource(poolId, convertToDBConfig(dbConfig));
            conn = ConnectionPoolManager.getConnection(poolId);
            metaData = conn.getMetaData();

            // 获取所有表名
            // 参数说明: catalog, schemaPattern, tableNamePattern, types
            String[] types = {"TABLE"};
            rs = metaData.getTables(null, null, "%", types);

            while (rs.next()) {
                tableNames.add(rs.getString("TABLE_NAME"));
            }

            // 关闭临时连接池
            ConnectionPoolManager.closeDataSource(poolId);

        } finally {
            closeResources(rs, null, conn);
        }

        return tableNames;
    }

    /**
     * 根据数据库配置获取指定表的表结构信息
     * @param dbConfig 数据库配置
     * @param tableName 表名
     * @return 表结构信息
     * @throws SQLException 数据库操作异常
     */
    public static TableSchema getTableSchema(DatabaseConfig dbConfig, String tableName) throws SQLException {
        Connection conn = null;
        DatabaseMetaData metaData = null;
        ResultSet rs = null;
        TableSchema schema = null;

        try {
            // 初始化连接池并获取连接
            String poolId = "temp_schema_pool_" + System.currentTimeMillis();
            ConnectionPoolManager.initDataSource(poolId, convertToDBConfig(dbConfig));
            conn = ConnectionPoolManager.getConnection(poolId);
            metaData = conn.getMetaData();

            // 获取表的结构信息
            String[] types = {"TABLE"};
            rs = metaData.getTables(null, null, tableName, types);

            if (rs.next()) {
                schema = new TableSchema();
                schema.setTableName(rs.getString("TABLE_NAME"));
                schema.setTableComment(rs.getString("REMARKS"));
            }

            // 关闭临时连接池
            ConnectionPoolManager.closeDataSource(poolId);

        } finally {
            closeResources(rs, null, conn);
        }

        return schema;
    }

    /**
     * 获取数据库服务器上的所有数据库
     * @param dbConfig 数据库配置
     * @return 数据库列表，包含数据库名称、类型和表数量等信息
     * @throws SQLException 数据库操作异常
     */
    public static List<Map<String, Object>> getAllDatabases(DatabaseConfig dbConfig) throws SQLException {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        List<Map<String, Object>> databases = new ArrayList<>();

        try {
            // 初始化连接池并获取连接
            String poolId = "temp_db_pool_" + System.currentTimeMillis();
            ConnectionPoolManager.initDataSource(poolId, convertToDBConfig(dbConfig));
            conn = ConnectionPoolManager.getConnection(poolId);

            String dbType = dbConfig.getDbType();
            String query = "";
            
            // 根据数据库类型执行不同的查询语句
            if ("MySQL".equals(dbType)) {
                query = "SHOW DATABASES";
            } else if ("Oracle".equals(dbType)) {
                query = "SELECT DISTINCT owner AS DATABASE_NAME FROM all_tables";
            } else if ("SQL Server".equals(dbType)) {
                query = "SELECT name FROM sys.databases";
            } else if ("PostgreSQL".equals(dbType)) {
                query = "SELECT datname FROM pg_database WHERE datistemplate = false";
            } else {
                // 默认使用MySQL的查询语句
                query = "SHOW DATABASES";
            }
            
            stmt = conn.createStatement();
            rs = stmt.executeQuery(query);
            
            while (rs.next()) {
                Map<String, Object> dbInfo = new HashMap<>();
                String dbName = "";
                
                if ("MySQL".equals(dbType) || "PostgreSQL".equals(dbType)) {
                    dbName = rs.getString(1);
                } else if ("Oracle".equals(dbType)) {
                    dbName = rs.getString("DATABASE_NAME");
                } else if ("SQL Server".equals(dbType)) {
                    dbName = rs.getString("name");
                } else {
                    dbName = rs.getString(1);
                }
                
                // 排除系统数据库
                if (!isSystemDatabase(dbName, dbType)) {
                    dbInfo.put("dbName", dbName);
                    dbInfo.put("dbType", dbType);
                    
                    // 获取数据库中的表数量（这里简化处理，实际可能需要额外查询）
                    dbInfo.put("tablesCount", 0);
                    // 假设创建时间（实际可能需要额外查询）
                    dbInfo.put("createTime", new java.util.Date());
                    
                    databases.add(dbInfo);
                }
            }
            
            // 关闭临时连接池
            ConnectionPoolManager.closeDataSource(poolId);
            
        } finally {
            closeResources(rs, stmt, conn);
        }
        
        return databases;
    }
    
    /**
     * 判断是否为系统数据库
     * @param dbName 数据库名称
     * @param dbType 数据库类型
     * @return 是否为系统数据库
     */
    private static boolean isSystemDatabase(String dbName, String dbType) {
        if ("MySQL".equals(dbType)) {
            return dbName.equals("mysql") || dbName.equals("information_schema") || 
                   dbName.equals("performance_schema") || dbName.equals("sys");
        } else if ("Oracle".equals(dbType)) {
            return dbName.equals("SYS") || dbName.equals("SYSTEM") || 
                   dbName.equals("SYSMAN") || dbName.equals("DBSNMP");
        } else if ("SQL Server".equals(dbType)) {
            return dbName.equals("master") || dbName.equals("model") || 
                   dbName.equals("msdb") || dbName.equals("tempdb");
        } else if ("PostgreSQL".equals(dbType)) {
            return dbName.equals("template0") || dbName.equals("template1") || 
                   dbName.equals("postgres");
        }
        return false;
    }
    
    /**
     * 同步指定数据库的表结构
     * @param dbConfig 数据库配置
     * @param databaseName 数据库名称
     * @return 是否同步成功
     */
    public static boolean syncDatabaseSchema(DatabaseConfig dbConfig, String databaseName) {
        try {
            // 注意：实际实现需要考虑事务管理
            // 1. 切换到指定数据库
            String originalUrl = dbConfig.getUrl();
            String newUrl = getDatabaseUrlWithDbName(dbConfig.getDbType(), originalUrl, databaseName);
            
            DatabaseConfig tempConfig = new DatabaseConfig();
            tempConfig.setUrl(newUrl);
            tempConfig.setUsername(dbConfig.getUsername());
            tempConfig.setPassword(dbConfig.getPassword());
            tempConfig.setDriverClassName(dbConfig.getDriverClassName());
            tempConfig.setMaximumPoolSize(dbConfig.getMaximumPoolSize());
            tempConfig.setMinimumIdle(dbConfig.getMinimumIdle());
            tempConfig.setConnectionTimeout(dbConfig.getConnectionTimeout());
            tempConfig.setIdleTimeout(dbConfig.getIdleTimeout());
            tempConfig.setMaxLifetime(dbConfig.getMaxLifetime());
            
            // 2. 获取该数据库中的所有表
            List<String> tables = getAllTableNames(tempConfig);
            
            // 3. 对于每个表，获取并保存表结构信息
            // 注意：这里只是演示逻辑，实际需要调用TableSchemaService和TableColumnService的相关方法
            // 为了简化代码，这里仅返回成功
            
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }
    
    /**
     * 根据数据库类型和原始URL，生成包含数据库名称的新URL
     * @param dbType 数据库类型
     * @param originalUrl 原始URL
     * @param databaseName 数据库名称
     * @return 包含数据库名称的新URL
     */
    private static String getDatabaseUrlWithDbName(String dbType, String originalUrl, String databaseName) {
        if ("MySQL".equals(dbType) || "PostgreSQL".equals(dbType)) {
            // 对于MySQL和PostgreSQL，URL格式通常包含数据库名称
            // 这里简化处理，实际可能需要更复杂的解析和替换逻辑
            int lastSlashIndex = originalUrl.lastIndexOf('/');
            int questionIndex = originalUrl.indexOf('?');
            
            if (questionIndex > 0) {
                if (lastSlashIndex < questionIndex) {
                    return originalUrl.substring(0, lastSlashIndex + 1) + databaseName + originalUrl.substring(questionIndex);
                }
            } else {
                if (lastSlashIndex > 0) {
                    return originalUrl.substring(0, lastSlashIndex + 1) + databaseName;
                }
            }
            return originalUrl + '/' + databaseName;
        } else if ("Oracle".equals(dbType)) {
            // Oracle数据库通常使用SID或服务名
            // 这里简化处理，实际可能需要修改连接属性
            return originalUrl;
        } else if ("SQL Server".equals(dbType)) {
            // SQL Server数据库URL通常包含数据库名称参数
            if (originalUrl.contains("databaseName=")) {
                return originalUrl.replaceAll("databaseName=\\w+", "databaseName=" + databaseName);
            } else {
                return originalUrl + (originalUrl.contains("?") ? "&" : "?") + "databaseName=" + databaseName;
            }
        }
        return originalUrl;
    }

    /**
     * 将DatabaseConfig转换为DBConfig
     * @param databaseConfig 数据库配置实体
     * @return DBConfig对象
     */
    private static DBConfig convertToDBConfig(DatabaseConfig databaseConfig) {
        DBConfig dbConfig = new DBConfig();
        dbConfig.setUrl(databaseConfig.getUrl());
        dbConfig.setUsername(databaseConfig.getUsername());
        dbConfig.setPassword(databaseConfig.getPassword());
        dbConfig.setDriverClassName(databaseConfig.getDriverClassName());
        dbConfig.setMaximumPoolSize(databaseConfig.getMaximumPoolSize());
        dbConfig.setMinimumIdle(databaseConfig.getMinimumIdle());
        dbConfig.setConnectionTimeout(databaseConfig.getConnectionTimeout());
        dbConfig.setIdleTimeout(databaseConfig.getIdleTimeout());
        dbConfig.setMaxLifetime(databaseConfig.getMaxLifetime());
        return dbConfig;
    }

    /**
     * 关闭数据库资源
     * @param rs 结果集
     * @param stmt 语句
     * @param conn 连接
     */
    private static void closeResources(ResultSet rs, Statement stmt, Connection conn) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                if (!conn.isClosed()) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}